 /**
   * Copyright 2014 Viettel Telecom. All rights reserved.
   * VIETTEL PROPRIETARY/CONFIDENTIAL. Use is subject to license terms.
   */
 package com.viettel.core.dao.cms; 
import java.util.ArrayList;
import java.util.List;

import org.hibernate.type.StandardBasicTypes;
import org.hibernate.type.Type;
import org.springframework.beans.factory.annotation.Autowired;

import com.viettel.core.common.utils.KPaging;
import com.viettel.core.common.utils.StringUtil;
import com.viettel.core.entities.cms.Article;
import com.viettel.core.entities.cms.ArticleCategory;
import com.viettel.core.entities.cms.CmsUser;
import com.viettel.core.entities.cms.ExpertGroup;
import com.viettel.core.entities.cms.GroupCategory;
import com.viettel.core.entities.cms.Media;
import com.viettel.core.entities.cms.RegimensDetail;
import com.viettel.core.entities.cms.TechniqueCategory;
import com.viettel.core.entities.vo.ArticleRegimensSearchVO;
import com.viettel.core.entities.vo.ArticleSearchFilter;
import com.viettel.core.entities.vo.ArticleSearchVO;
import com.viettel.core.enumtype.ArticleCategoryStatus;
import com.viettel.core.enumtype.ArticleStatus;
import com.viettel.core.enumtype.ArticleType;
import com.viettel.core.enumtype.ExpertGroupStatus;
import com.viettel.core.exceptions.DataAccessException;
import com.viettel.core.repo.IRepository;
 /**
   *  Mo ta muc dich cua lop (interface)
   *  @author: TOANTM1
   *  @version:  
   *  @since: 27/02/2014 - 11:34:02
   */
 public class ArticleDAOImpl implements ArticleDAO { 
	 private IRepository repo; 
		
	 	public void setRepo(IRepository repo) {
			this.repo = repo;
		} 
 
 	/**@author TOANTM1
  	 * 27/02/2014 - 11:34:02
  	 * For: 
  	 * @description: 
  	 */
 	@Override 
	public Article getArticleById(Integer id) throws DataAccessException { 
		return repo.getEntityById(Article.class, id); 
	} 
 
 	/**@author TOANTM1
  	 * 27/02/2014 - 11:34:02
  	 * For: 
  	 * @description: 
  	 */
 	@Override 
	public Article createArticle(Article article) 
			throws DataAccessException { 
		if (article == null) { 
			throw new IllegalArgumentException("article"); 
		} 
		repo.create(article); 
		return repo.getEntityById(Article.class, article.getId()); 
	} 
 
 	/**@author TOANTM1
  	 * 27/02/2014 - 11:34:02
  	 * For: 
  	 * @description: 
  	 */
 	@Override 
	public void updateArticle(Article article) throws DataAccessException { 
		if (article == null) { 
			throw new IllegalArgumentException("article"); 
		} 
		repo.update(article); 
	} 
 
 	/**@author TOANTM1
  	 * 27/02/2014 - 11:34:02
  	 * For: 
  	 * @description: 
  	 */
 	@Override 
	public void deleteArticle(Article article) throws DataAccessException { 
		if (article == null) { 
			throw new IllegalArgumentException("article"); 
		} 
		repo.delete(article); 
	}

 	@Override
	public List<ArticleSearchVO> getListArticleCategoryByFilter(
			ArticleSearchFilter filter) throws DataAccessException {
		List<Object> paramSelect = new ArrayList<Object>();
		List<Object> countParams = new ArrayList<Object>();
		StringBuilder selectSql = new StringBuilder();
		StringBuilder countQuery = new StringBuilder();
		StringBuilder fromQuery = new StringBuilder();
		
		if (filter.getRowNum() != null) {
			selectSql.append(" SELECT * FROM ( ");
		}
		//dsdasdasd
		selectSql.append(" SELECT AR.id 	as id, ");
		selectSql.append(" AR.name 			as name, ");
		selectSql.append(" ME.id 			as mediaId, ");
		selectSql.append(" ME.name 			as mediaName, ");
		selectSql.append(" AR.avatar_url 	as avatarUrl, ");
		selectSql.append(" AR.avatar_thumb 	as avatarThumb, ");
		selectSql.append(" AR.content 		as content, ");
		selectSql.append(" AR.content_html 	as contentHtml, ");
		selectSql.append(" AR.sms_content 	as smsContent, ");
		selectSql.append(" AG.id 			as articleCategoryId, ");
		selectSql.append(" AG.name 			as articleCategoryName, ");
		selectSql.append(" GC.id 			as groupCategoryId, ");
		selectSql.append(" GC.name 			as groupCategoryName, ");
		selectSql.append(" TE.id 			as techniqueCategoryId, ");
		selectSql.append(" TE.name 			as techniqueCategoryName, ");
		selectSql.append(" AR.status 		as status, ");
		selectSql.append(" AR.type 			as type, ");
		selectSql.append(" AR.tags 			as tags, ");
		selectSql.append(" US.id 			as cmsUserId, ");
		selectSql.append(" US.username 		as createdUser, ");
		if(filter.getIsForMobileServer()){
			/**
			 * EXAMPLE sysdate - AR.created_time = 5 3:25:45
			 * 
			 * LAY TU VI TRI 1 DEN VI TRI SPACE ' ' 
			 * (SUBSTR(sysdate - AR.created_time,1,(Instr (sysdate - AR.created_time, ' ', 1))-1)) = 5
			 * LAY TU DAU ':' LUI VE SAU 2 KY TU DEN VI TRI ':'
			 * SUBSTR(sysdate - AR.created_time, (Instr (sysdate - AR.created_time, ':', 1)-2), 2 ) = 3
			 */
			selectSql.append("  ( \n");
			selectSql.append("  CASE \n");
			selectSql.append("    WHEN (SUBSTR(sysdate - AR.created_time,1,(Instr (sysdate - AR.created_time, ' ', 1))-1)) >0 \n");
			selectSql.append("    THEN '(' \n");
			selectSql.append("      || to_char(AR.created_time,'dd/MM/yyyy') \n");
			selectSql.append("      || ')' \n");
			selectSql.append("    WHEN (SUBSTR(sysdate - AR.created_time,1,(Instr (sysdate - AR.created_time, ' ', 1))-1)) <=0 \n");
			selectSql.append("    THEN '(vài phút trước)' \n");
			selectSql.append("    ELSE '(' \n");
			selectSql.append("      || FLOOR(SUBSTR(sysdate - AR.created_time, (Instr (sysdate - AR.created_time, ':', 1)-2), 2 )) \n");
			selectSql.append("      || ' giờ trước)' \n");
			selectSql.append("  END )         AS createdDate, \n");
		}else{
			selectSql.append(" AR.created_time 	as createdDate, ");
		}
		selectSql.append(" AR.created_time 	as createdTime, ");
		selectSql.append(" AR.sent_count	as sentCount ");
		
		fromQuery.append(" FROM ARTICLE AR ");
		fromQuery.append(" 		LEFT JOIN ARTICLE_CATEGORY AG ");
		fromQuery.append(" 			ON AR.article_category_id = AG.id ");
		fromQuery.append(" 		LEFT JOIN GROUP_CATEGORY GC ");
		fromQuery.append(" 			ON AR.group_category_id = GC.id ");
		fromQuery.append(" 		LEFT JOIN TECHNIQUE_CATEGORY TE ");
		fromQuery.append(" 			ON AR.technique_category_id = TE.id ");
		fromQuery.append(" 		LEFT JOIN CMS_USER US ");
		fromQuery.append(" 			ON AR.cms_user_id = US.id ");
		fromQuery.append(" 		LEFT JOIN MEDIA ME ");
		fromQuery.append(" 			ON AR.media_id = ME.id ");
		
		fromQuery.append(" where AR.status != ? ");
		paramSelect.add(ArticleStatus.DELETED.getValue());
		countParams.add(ArticleStatus.DELETED.getValue());
		
		fromQuery.append(" and TE.status != ? ");
		paramSelect.add(ArticleCategoryStatus.DELETED.getValue());
		countParams.add(ArticleCategoryStatus.DELETED.getValue());
		
		if (filter.getType() != null) {
			fromQuery.append(" and AR.type = ? ");
			paramSelect.add(filter.getType());
			countParams.add(filter.getType());
		}
		
		if (filter.getId() != null) {
			fromQuery.append(" and AR.id = ? ");
			paramSelect.add(filter.getId());
			countParams.add(filter.getId());
		}
		
		if (!StringUtil.isNullOrEmpty(filter.getNameText())) {
			fromQuery.append("AND lower(ar.name_text) LIKE lower(?) escape '/' ");
			paramSelect.add("%" + StringUtil.toSearchText(filter.getNameText()) + "%");
			countParams.add("%" + StringUtil.toSearchText(filter.getNameText()) + "%");
		}
		
		if (!StringUtil.isNullOrEmpty(filter.getTags())) {
			fromQuery.append("AND lower(ar.tags) LIKE lower(?) escape '/' ");
			paramSelect.add("%" + StringUtil.toSearchText(filter.getTags()) + "%");
			countParams.add("%" + StringUtil.toSearchText(filter.getTags()) + "%");
		}
		
		if (!StringUtil.isNullOrEmpty(filter.getContentText())) {
			fromQuery.append("AND lower(ar.content_text) LIKE lower(?) escape '/' ");
			paramSelect.add("%" + StringUtil.toSearchText(filter.getContentText()) + "%");
			countParams.add("%" + StringUtil.toSearchText(filter.getContentText()) + "%");
		}
		
		if (filter.getMediaId() != null) {
			fromQuery.append(" and AR.media_id = ? ");
			paramSelect.add(filter.getMediaId());
			countParams.add(filter.getMediaId());
		}
		
		if (filter.getArticleCategoryId() != null) {
			fromQuery.append(" and AR.article_category_id = ? ");
			paramSelect.add(filter.getArticleCategoryId());
			countParams.add(filter.getArticleCategoryId());
		}
		
		if (filter.getGroupCategoryId() != null) {
			fromQuery.append(" and ( GC.ID = ? OR GC.PARENT_ID = ? )");
			paramSelect.add(filter.getGroupCategoryId());
			countParams.add(filter.getGroupCategoryId());
			paramSelect.add(filter.getGroupCategoryId());
			countParams.add(filter.getGroupCategoryId());
		}
		
		if (filter.getTechniqueCategoryId() != null) {
			fromQuery.append(" and ( TE.ID = ? OR TE.PARENT_ID = ? ) ");
			paramSelect.add(filter.getTechniqueCategoryId());
			countParams.add(filter.getTechniqueCategoryId());
			paramSelect.add(filter.getTechniqueCategoryId());
			countParams.add(filter.getTechniqueCategoryId());
		} 
		if (filter.getStatus() != null) {
			fromQuery.append(" and AR.status = ? ");
			paramSelect.add(filter.getStatus());
			countParams.add(filter.getStatus());
		}
		
		if(filter.getFromDate()!=null && filter.getToDate()!=null ){
			fromQuery.append("  AND TRUNC(AR.CREATED_TIME) BETWEEN TRUNC(?) AND TRUNC(?) ");
			paramSelect.add(filter.getFromDate());
			countParams.add(filter.getFromDate());
			
			paramSelect.add(filter.getToDate());
			countParams.add(filter.getToDate());
		}
	
		if (filter.getIsSortView()!=null && filter.getIsSortView()==true)
			selectSql.append(fromQuery).append(" ORDER BY AR.num_view DESC, AR.created_time DESC, AR.id DESC ");
		else
			selectSql.append(fromQuery).append(" ORDER BY AR.created_time DESC, AR.id DESC ");

		if (filter.getRowNum() != null) {
			selectSql.append(" ) WHERE rownum <= ? ");
			paramSelect.add(filter.getRowNum());
			countParams.add(filter.getRowNum());
		}
		
		countQuery.append("SELECT COUNT(*) AS COUNT ").append(fromQuery);

		String[] fieldNames = new String[] { 
				"id", "name", "mediaId",
				"mediaName", "avatarUrl", "avatarThumb", 
				"content",  "contentHtml", "smsContent",
				"articleCategoryId","articleCategoryName",
				"groupCategoryId","groupCategoryName",
				"techniqueCategoryId","techniqueCategoryName",
				"status", "type", "cmsUserId",
				"createdUser","createdTime","sentCount", 
				"createdDate", "tags"
				};
		Type[] fieldTypes = new Type[] { 
				StandardBasicTypes.INTEGER,StandardBasicTypes.STRING, StandardBasicTypes.INTEGER,
				StandardBasicTypes.STRING, StandardBasicTypes.STRING, StandardBasicTypes.STRING, 
				StandardBasicTypes.STRING, StandardBasicTypes.STRING, StandardBasicTypes.STRING,
				StandardBasicTypes.INTEGER,StandardBasicTypes.STRING,
				StandardBasicTypes.INTEGER,StandardBasicTypes.STRING, 
				StandardBasicTypes.INTEGER,StandardBasicTypes.STRING, 
				StandardBasicTypes.INTEGER,StandardBasicTypes.INTEGER, StandardBasicTypes.INTEGER, 
				StandardBasicTypes.STRING, StandardBasicTypes.TIMESTAMP, StandardBasicTypes.INTEGER,
				StandardBasicTypes.STRING, StandardBasicTypes.STRING};

		List<Class<?>> synchronizedClass = new ArrayList<Class<?>>();
		synchronizedClass.add(Article.class);
		synchronizedClass.add(Media.class);
		synchronizedClass.add(ArticleCategory.class);
		synchronizedClass.add(GroupCategory.class);
		synchronizedClass.add(TechniqueCategory.class);
		synchronizedClass.add(CmsUser.class);

		List<ArticleSearchVO> list = null;
		if (filter.getPaging() != null) {
			list = repo.getListByQueryAndScalarPaginated(
					ArticleSearchVO.class, fieldNames, fieldTypes,
					selectSql.toString(), countQuery.toString(), paramSelect,
					countParams, filter.getPaging(), synchronizedClass);
		} else {
			list = repo.getListByQueryAndScalar(ArticleSearchVO.class,
					fieldNames, fieldTypes, selectSql.toString(), paramSelect,
					synchronizedClass);
		}

		return list;
	}
 	
	@Override
	public List<ArticleSearchVO> getListArticleByFilter(
			ArticleSearchFilter filter) throws DataAccessException {
		List<Object> paramSelect = new ArrayList<Object>();
		List<Object> countParams = new ArrayList<Object>();
		StringBuilder selectSql = new StringBuilder();
		StringBuilder countQuery = new StringBuilder();
		StringBuilder fromQuery = new StringBuilder();
		
		if (filter.getRowNum() != null) {
			selectSql.append(" SELECT * FROM ( ");
		}
		//dsdasdasd
		selectSql.append(" SELECT AR.id 	as id, ");
		selectSql.append(" AR.name 			as name, ");
		selectSql.append(" ME.id 			as mediaId, ");
		selectSql.append(" ME.name 			as mediaName, ");
		selectSql.append(" AR.avatar_url 	as avatarUrl, ");
		selectSql.append(" AR.avatar_thumb 	as avatarThumb, ");
		selectSql.append(" AR.content 		as content, ");
		selectSql.append(" AR.content_html 	as contentHtml, ");
		selectSql.append(" AR.sms_content 	as smsContent, ");
		selectSql.append(" AG.id 			as articleCategoryId, ");
		selectSql.append(" AG.name 			as articleCategoryName, ");
		selectSql.append(" GC.id 			as groupCategoryId, ");
		selectSql.append(" GC.name 			as groupCategoryName, ");
		selectSql.append(" TE.id 			as techniqueCategoryId, ");
		selectSql.append(" TE.name 			as techniqueCategoryName, ");
		selectSql.append(" AR.status 		as status, ");
		selectSql.append(" AR.type 			as type, ");
		selectSql.append(" AR.tags 			as tags, ");
		selectSql.append(" US.id 			as cmsUserId, ");
		selectSql.append(" US.username 		as createdUser, ");
		if(filter.getIsForMobileServer()){
			/**
			 * EXAMPLE sysdate - AR.created_time = 5 3:25:45
			 * 
			 * LAY TU VI TRI 1 DEN VI TRI SPACE ' ' 
			 * (SUBSTR(sysdate - AR.created_time,1,(Instr (sysdate - AR.created_time, ' ', 1))-1)) = 5
			 * LAY TU DAU ':' LUI VE SAU 2 KY TU DEN VI TRI ':'
			 * SUBSTR(sysdate - AR.created_time, (Instr (sysdate - AR.created_time, ':', 1)-2), 2 ) = 3
			 */
			selectSql.append("  ( \n");
			selectSql.append("  CASE \n");
			selectSql.append("    WHEN (SUBSTR(sysdate - AR.created_time,1,(Instr (sysdate - AR.created_time, ' ', 1))-1)) >0 \n");
			selectSql.append("    THEN '(' \n");
			selectSql.append("      || to_char(AR.created_time,'dd/MM/yyyy') \n");
			selectSql.append("      || ')' \n");
			selectSql.append("    WHEN (SUBSTR(sysdate - AR.created_time,1,(Instr (sysdate - AR.created_time, ' ', 1))-1)) <=0 \n");
			selectSql.append("    THEN '(vài phút trước)' \n");
			selectSql.append("    ELSE '(' \n");
			selectSql.append("      || FLOOR(SUBSTR(sysdate - AR.created_time, (Instr (sysdate - AR.created_time, ':', 1)-2), 2 )) \n");
			selectSql.append("      || ' giờ trước)' \n");
			selectSql.append("  END )         AS createdDate, \n");
		}else{
			selectSql.append(" AR.created_time 	as createdDate, ");
		}
		selectSql.append(" AR.created_time 	as createdTime, ");
		selectSql.append(" AR.sent_count	as sentCount ");
		
		fromQuery.append(" FROM ARTICLE AR ");
		fromQuery.append(" 		LEFT JOIN ARTICLE_CATEGORY AG ");
		fromQuery.append(" 			ON AR.article_category_id = AG.id ");
		fromQuery.append(" 		LEFT JOIN GROUP_CATEGORY GC ");
		fromQuery.append(" 			ON AR.group_category_id = GC.id ");
		fromQuery.append(" 		LEFT JOIN TECHNIQUE_CATEGORY TE ");
		fromQuery.append(" 			ON AR.technique_category_id = TE.id ");
		fromQuery.append(" 		LEFT JOIN CMS_USER US ");
		fromQuery.append(" 			ON AR.cms_user_id = US.id ");
		fromQuery.append(" 		LEFT JOIN MEDIA ME ");
		fromQuery.append(" 			ON AR.media_id = ME.id ");
		
		fromQuery.append(" where (AR.status != ? ");
		paramSelect.add(ArticleStatus.DELETED.getValue());
		countParams.add(ArticleStatus.DELETED.getValue());
		
		fromQuery.append(" and TE.status != ? ");
		paramSelect.add(ArticleCategoryStatus.DELETED.getValue());
		countParams.add(ArticleCategoryStatus.DELETED.getValue());
		
		if (filter.getType() != null) {
			fromQuery.append(" and AR.type = ? ");
			paramSelect.add(filter.getType());
			countParams.add(filter.getType());
		}
		
		if (filter.getId() != null) {
			fromQuery.append(" and AR.id = ? ");
			paramSelect.add(filter.getId());
			countParams.add(filter.getId());
		}
		
		if (!StringUtil.isNullOrEmpty(filter.getNameText())) {
			fromQuery.append("AND lower(ar.name_text) LIKE lower(?) escape '/' ");
			paramSelect.add("%" + StringUtil.toSearchText(filter.getNameText()) + "%");
			countParams.add("%" + StringUtil.toSearchText(filter.getNameText()) + "%");
		}
		
		if (!StringUtil.isNullOrEmpty(filter.getTags())) {
			fromQuery.append("AND lower(ar.tags) LIKE lower(?) escape '/' ");
			paramSelect.add("%" + StringUtil.toSearchText(filter.getTags()) + "%");
			countParams.add("%" + StringUtil.toSearchText(filter.getTags()) + "%");
		}
		
		if (!StringUtil.isNullOrEmpty(filter.getContentText())) {
			fromQuery.append("AND lower(ar.content_text) LIKE lower(?) escape '/' ");
			paramSelect.add("%" + StringUtil.toSearchText(filter.getContentText()) + "%");
			countParams.add("%" + StringUtil.toSearchText(filter.getContentText()) + "%");
		}
		
		if (filter.getMediaId() != null) {
			fromQuery.append(" and AR.media_id = ? ");
			paramSelect.add(filter.getMediaId());
			countParams.add(filter.getMediaId());
		}

		fromQuery.append(" and AR.STATUS != ? ");
		paramSelect.add(4);
		countParams.add(4);
		
		if (filter.getArticleCategoryId() != null) {
			fromQuery.append(" and AR.article_category_id = ? ");
			paramSelect.add(filter.getArticleCategoryId());
			countParams.add(filter.getArticleCategoryId());
		}
		
		if (filter.getGroupCategoryId() != null) {
			fromQuery.append(" and ( GC.ID = ? OR GC.PARENT_ID = ? )");
			paramSelect.add(filter.getGroupCategoryId());
			countParams.add(filter.getGroupCategoryId());
			paramSelect.add(filter.getGroupCategoryId());
			countParams.add(filter.getGroupCategoryId());
		}
		
		if (filter.getTechniqueCategoryId() != null) {
			fromQuery.append(" and ( TE.ID = ? OR TE.PARENT_ID = ? ) ");
			paramSelect.add(filter.getTechniqueCategoryId());
			countParams.add(filter.getTechniqueCategoryId());
			paramSelect.add(filter.getTechniqueCategoryId());
			countParams.add(filter.getTechniqueCategoryId());
		} 
		if (filter.getStatus() != null) {
			fromQuery.append(" and AR.status = ? ");
			paramSelect.add(filter.getStatus());
			countParams.add(filter.getStatus());
		}
		
		if(filter.getFromDate()!=null && filter.getToDate()!=null ){
			fromQuery.append("  AND TRUNC(AR.CREATED_TIME) BETWEEN TRUNC(?) AND TRUNC(?) )");
			paramSelect.add(filter.getFromDate());
			countParams.add(filter.getFromDate());
			
			paramSelect.add(filter.getToDate());
			countParams.add(filter.getToDate());
		}
		//Or
		if(filter.getCmsUserId() != null){
			fromQuery.append("  OR (AR.CMS_USER_ID = ?  ");
			paramSelect.add(filter.getCmsUserId());
			countParams.add(filter.getCmsUserId());
		}
		if (filter.getStatus() != null) {
			fromQuery.append(" AND AR.STATUS = ? ");
			paramSelect.add(filter.getStatus());
			countParams.add(filter.getStatus());
		}else{
			fromQuery.append(" AND AR.STATUS = ? ");
			paramSelect.add(4);
			countParams.add(4);
		}
		if (filter.getArticleCategoryId() != null) {
			fromQuery.append(" and AR.article_category_id = ? ");
			paramSelect.add(filter.getArticleCategoryId());
			countParams.add(filter.getArticleCategoryId());
		}
		
		if (filter.getGroupCategoryId() != null) {
			fromQuery.append(" and ( GC.ID = ? OR GC.PARENT_ID = ? )");
			paramSelect.add(filter.getGroupCategoryId());
			countParams.add(filter.getGroupCategoryId());
			paramSelect.add(filter.getGroupCategoryId());
			countParams.add(filter.getGroupCategoryId());
		}
		
		if (filter.getTechniqueCategoryId() != null) {
			fromQuery.append(" and ( TE.ID = ? OR TE.PARENT_ID = ? ) ");
			paramSelect.add(filter.getTechniqueCategoryId());
			countParams.add(filter.getTechniqueCategoryId());
			paramSelect.add(filter.getTechniqueCategoryId());
			countParams.add(filter.getTechniqueCategoryId());
		} 
		if (filter.getType() != null) {
			fromQuery.append(" and AR.type = ? ");
			paramSelect.add(filter.getType());
			countParams.add(filter.getType());
		}
		
		if (filter.getId() != null) {
			fromQuery.append(" and AR.id = ? ");
			paramSelect.add(filter.getId());
			countParams.add(filter.getId());
		}
		
		if (!StringUtil.isNullOrEmpty(filter.getNameText())) {
			fromQuery.append("AND lower(ar.name_text) LIKE lower(?) escape '/' ");
			paramSelect.add("%" + StringUtil.toSearchText(filter.getNameText()) + "%");
			countParams.add("%" + StringUtil.toSearchText(filter.getNameText()) + "%");
		}
		
		if (!StringUtil.isNullOrEmpty(filter.getTags())) {
			fromQuery.append("AND lower(ar.tags) LIKE lower(?) escape '/' ");
			paramSelect.add("%" + StringUtil.toSearchText(filter.getTags()) + "%");
			countParams.add("%" + StringUtil.toSearchText(filter.getTags()) + "%");
		}
		
		if (!StringUtil.isNullOrEmpty(filter.getContentText())) {
			fromQuery.append("AND lower(ar.content_text) LIKE lower(?) escape '/' ");
			paramSelect.add("%" + StringUtil.toSearchText(filter.getContentText()) + "%");
			countParams.add("%" + StringUtil.toSearchText(filter.getContentText()) + "%");
		}
		
		if (filter.getMediaId() != null) {
			fromQuery.append(" and AR.media_id = ? ");
			paramSelect.add(filter.getMediaId());
			countParams.add(filter.getMediaId());
		}
		if(filter.getFromDate()!=null && filter.getToDate()!=null ){
			fromQuery.append("  AND TRUNC(AR.CREATED_TIME) BETWEEN TRUNC(?) AND TRUNC(?) )");
			paramSelect.add(filter.getFromDate());
			countParams.add(filter.getFromDate());
			
			paramSelect.add(filter.getToDate());
			countParams.add(filter.getToDate());
		}
		//end Or
		if (filter.getIsSortView()!=null && filter.getIsSortView()==true)
			selectSql.append(fromQuery).append(" ORDER BY AR.num_view DESC, AR.created_time DESC, AR.id DESC ");
		else
			selectSql.append(fromQuery).append(" ORDER BY AR.created_time DESC, AR.id DESC ");

		if (filter.getRowNum() != null) {
			selectSql.append(" ) WHERE rownum <= ? ");
			paramSelect.add(filter.getRowNum());
			countParams.add(filter.getRowNum());
		}
		
		countQuery.append("SELECT COUNT(*) AS COUNT ").append(fromQuery);

		String[] fieldNames = new String[] { 
				"id", "name", "mediaId",
				"mediaName", "avatarUrl", "avatarThumb", 
				"content",  "contentHtml", "smsContent",
				"articleCategoryId","articleCategoryName",
				"groupCategoryId","groupCategoryName",
				"techniqueCategoryId","techniqueCategoryName",
				"status", "type", "cmsUserId",
				"createdUser","createdTime","sentCount", 
				"createdDate", "tags"
				};
		Type[] fieldTypes = new Type[] { 
				StandardBasicTypes.INTEGER,StandardBasicTypes.STRING, StandardBasicTypes.INTEGER,
				StandardBasicTypes.STRING, StandardBasicTypes.STRING, StandardBasicTypes.STRING, 
				StandardBasicTypes.STRING, StandardBasicTypes.STRING, StandardBasicTypes.STRING,
				StandardBasicTypes.INTEGER,StandardBasicTypes.STRING,
				StandardBasicTypes.INTEGER,StandardBasicTypes.STRING, 
				StandardBasicTypes.INTEGER,StandardBasicTypes.STRING, 
				StandardBasicTypes.INTEGER,StandardBasicTypes.INTEGER, StandardBasicTypes.INTEGER, 
				StandardBasicTypes.STRING, StandardBasicTypes.TIMESTAMP, StandardBasicTypes.INTEGER,
				StandardBasicTypes.STRING, StandardBasicTypes.STRING};

		List<Class<?>> synchronizedClass = new ArrayList<Class<?>>();
		synchronizedClass.add(Article.class);
		synchronizedClass.add(Media.class);
		synchronizedClass.add(ArticleCategory.class);
		synchronizedClass.add(GroupCategory.class);
		synchronizedClass.add(TechniqueCategory.class);
		synchronizedClass.add(CmsUser.class);

		List<ArticleSearchVO> list = null;
		if (filter.getPaging() != null) {
			list = repo.getListByQueryAndScalarPaginated(
					ArticleSearchVO.class, fieldNames, fieldTypes,
					selectSql.toString(), countQuery.toString(), paramSelect,
					countParams, filter.getPaging(), synchronizedClass);
		} else {
			list = repo.getListByQueryAndScalar(ArticleSearchVO.class,
					fieldNames, fieldTypes, selectSql.toString(), paramSelect,
					synchronizedClass);
		}

		return list;
	}

	@Override
	public List<ArticleRegimensSearchVO> getListSearchArticleAndRegimens(
			String content, KPaging<ArticleRegimensSearchVO> paging)
			throws DataAccessException {
		List<Object> param = new ArrayList<Object>();
		StringBuilder selectSql = new StringBuilder();
		StringBuilder countQuery = new StringBuilder();
			
		selectSql.append(" SELECT AT.id 			AS objectId, ");
		selectSql.append(" AT.id 					AS articleId, ");
		selectSql.append(" SUBSTR(AT.content,0,100) AS content, ");
		selectSql.append(" AT.name 					AS name, ");
		selectSql.append(" AT.type 					AS articleType ");
		selectSql.append(" FROM article AT ");
		selectSql.append(" WHERE (upper(AT.content_text) LIKE upper(?) OR upper(AT.name_text) LIKE upper(?))  and rownum <=30 ");
		param.add("%" + content + "%");
		param.add("%" + content + "%");
		selectSql.append(" UNION ALL ");
		selectSql.append(" SELECT RE.id     		AS objectId, ");
		selectSql.append(" AT2.id 					AS articleId, ");
		selectSql.append(" SUBSTR(RE.content,0,100) AS content, ");
		selectSql.append(" AT2.name 				AS name, ");
		selectSql.append(" AT2.type 				AS articleType ");
		selectSql.append(" FROM article AT2 ");
		selectSql.append(" JOIN regimens_detail RE ");
		selectSql.append(" ON RE.article_id = AT2.id ");
		selectSql.append(" WHERE (upper(RE.content_text) LIKE upper(?) OR upper(AT2.name_text) LIKE upper(?)) and rownum <=30 ");
		param.add("%" + content + "%");
		param.add("%" + content + "%");

		countQuery.append("SELECT COUNT(*) AS COUNT FROM ( ").append(selectSql).append(") ");

		String[] fieldNames = new String[] { 
				"objectId","articleId", "content", "name", "articleType"
				};
		Type[] fieldTypes = new Type[] { 
				StandardBasicTypes.INTEGER,StandardBasicTypes.INTEGER,StandardBasicTypes.STRING, 
				StandardBasicTypes.STRING, StandardBasicTypes.INTEGER
				};

		List<Class<?>> synchronizedClass = new ArrayList<Class<?>>();
		synchronizedClass.add(Article.class);
		synchronizedClass.add(RegimensDetail.class);

		List<ArticleRegimensSearchVO> list = null;
		if (paging != null) {
			list = repo.getListByQueryAndScalarPaginated(
					ArticleRegimensSearchVO.class, fieldNames, fieldTypes,
					selectSql.toString(), countQuery.toString(), param,
					param, paging, synchronizedClass);
		} else {
			list = repo.getListByQueryAndScalar(ArticleRegimensSearchVO.class,
					fieldNames, fieldTypes, selectSql.toString(), param,
					synchronizedClass);
		}

		return list;
	} 
	
	@Override
	public List<ArticleSearchVO> getListArticleByArticleCategoryId(Integer articleCategoryId, Integer rowNum)
			throws DataAccessException {
		List<Object> paramSelect = new ArrayList<Object>();
		List<Object> countParams = new ArrayList<Object>();
		StringBuilder selectSql = new StringBuilder();
		StringBuilder countQuery = new StringBuilder();
		StringBuilder fromQuery = new StringBuilder();
		
		if (rowNum != null) {
			selectSql.append(" SELECT * FROM ( ");
		}
		
		selectSql.append(" SELECT AR.id 	as id, ");
		selectSql.append(" AR.name 			as name, ");
		selectSql.append(" ME.id 			as mediaId, ");
		selectSql.append(" ME.name 			as mediaName, ");
		selectSql.append(" AR.avatar_url 	as avatarUrl, ");
		selectSql.append(" AR.avatar_thumb 	as avatarThumb, ");
		selectSql.append(" AR.content 		as content, ");
		selectSql.append(" AR.content_html 	as contentHtml, ");
		selectSql.append(" AG.id 			as articleCategoryId, ");
		selectSql.append(" AG.name 			as articleCategoryName, ");
		selectSql.append(" GC.id 			as groupCategoryId, ");
		selectSql.append(" GC.name 			as groupCategoryName, ");
		selectSql.append(" TE.id 			as techniqueCategoryId, ");
		selectSql.append(" TE.name 			as techniqueCategoryName, ");
		selectSql.append(" AR.status 		as status, ");
		selectSql.append(" AR.type 			as type, ");
		selectSql.append(" US.id 			as cmsUserId, ");
		selectSql.append(" US.username 		as createdUser, ");
		selectSql.append(" AR.created_time 	as createdTime, ");
		selectSql.append(" AR.sent_count	as sentCount ");
		
		fromQuery.append(" FROM ARTICLE AR ");
		fromQuery.append(" 		LEFT JOIN ARTICLE_CATEGORY AG ");
		fromQuery.append(" 			ON AR.article_category_id = AG.id ");
		fromQuery.append(" 		LEFT JOIN GROUP_CATEGORY GC ");
		fromQuery.append(" 			ON AR.group_category_id = GC.id ");
		fromQuery.append(" 		LEFT JOIN TECHNIQUE_CATEGORY TE ");
		fromQuery.append(" 			ON AR.technique_category_id = TE.id ");
		fromQuery.append(" 		LEFT JOIN CMS_USER US ");
		fromQuery.append(" 			ON AR.cms_user_id = US.id ");
		fromQuery.append(" 		LEFT JOIN MEDIA ME ");
		fromQuery.append(" 			ON AR.media_id = ME.id ");
		
		fromQuery.append(" where AR.status != ? AND AR.type = ? ");
		paramSelect.add(ArticleStatus.DELETED.getValue());
		countParams.add(ArticleStatus.DELETED.getValue());
		
		paramSelect.add(ArticleType.TYPE_ARTICLE.getValue());
		countParams.add(ArticleType.TYPE_ARTICLE.getValue());
		
		if (articleCategoryId != null) {
			fromQuery.append(" and AR.article_category_id = ? ");
			paramSelect.add(articleCategoryId);
			countParams.add(articleCategoryId);
		}
		
		selectSql.append(fromQuery).append(" ORDER BY AR.created_time DESC");

		if (rowNum != null) {
			selectSql.append(" ) WHERE rownum <= ? ");
			paramSelect.add(rowNum);
			countParams.add(rowNum);
		}
		
		countQuery.append("SELECT COUNT(*) AS COUNT ").append(fromQuery);

		String[] fieldNames = new String[] { 
				"id", "name", "mediaId",
				"mediaName", "avatarUrl", "avatarThumb", 
				"content",  "contentHtml", 
				"articleCategoryId","articleCategoryName",
				"groupCategoryId","groupCategoryName",
				"techniqueCategoryId","techniqueCategoryName",
				"status", "type", "cmsUserId",
				"createdUser","createdTime","sentCount"
				};
		Type[] fieldTypes = new Type[] { 
				StandardBasicTypes.INTEGER,StandardBasicTypes.STRING, StandardBasicTypes.INTEGER,
				StandardBasicTypes.STRING, StandardBasicTypes.STRING, StandardBasicTypes.STRING, 
				StandardBasicTypes.STRING, StandardBasicTypes.STRING, 
				StandardBasicTypes.INTEGER,StandardBasicTypes.STRING,
				StandardBasicTypes.INTEGER,StandardBasicTypes.STRING, 
				StandardBasicTypes.INTEGER,StandardBasicTypes.STRING, 
				StandardBasicTypes.INTEGER,StandardBasicTypes.INTEGER, StandardBasicTypes.INTEGER, 
				StandardBasicTypes.STRING, StandardBasicTypes.TIMESTAMP, StandardBasicTypes.INTEGER };

		List<Class<?>> synchronizedClass = new ArrayList<Class<?>>();
		synchronizedClass.add(Article.class);
		synchronizedClass.add(Media.class);
		synchronizedClass.add(ArticleCategory.class);
		synchronizedClass.add(GroupCategory.class);
		synchronizedClass.add(TechniqueCategory.class);
		synchronizedClass.add(CmsUser.class);

		List<ArticleSearchVO> list = null;
		list = repo.getListByQueryAndScalar(ArticleSearchVO.class,
				fieldNames, fieldTypes, selectSql.toString(), paramSelect,
				synchronizedClass);
			
		return list;
		
		
	}
	@Override
	public List<ArticleSearchVO> getListArticleById(Integer id)
			throws DataAccessException {
		List<Object> paramSelect = new ArrayList<Object>();
		List<Object> countParams = new ArrayList<Object>();
		StringBuilder selectSql = new StringBuilder();
		StringBuilder countQuery = new StringBuilder();
		StringBuilder fromQuery = new StringBuilder();
		
		
		selectSql.append(" SELECT AR.id 	as id, ");
		selectSql.append(" AR.name 			as name, ");
		selectSql.append(" ME.id 			as mediaId, ");
		selectSql.append(" ME.name 			as mediaName, ");
		selectSql.append(" AR.avatar_url 	as avatarUrl, ");
		selectSql.append(" AR.avatar_thumb 	as avatarThumb, ");
		selectSql.append(" AR.content 		as content, ");
		selectSql.append(" AR.content_html 	as contentHtml, ");
		selectSql.append(" AR.reason 		as reason, ");
		selectSql.append(" AR.reason_html 	as reasonHtml, ");
		selectSql.append(" AG.id 			as articleCategoryId, ");
		selectSql.append(" AG.name 			as articleCategoryName, ");
		selectSql.append(" GC.id 			as groupCategoryId, ");
		selectSql.append(" GC.name 			as groupCategoryName, ");
		selectSql.append(" TE.id 			as techniqueCategoryId, ");
		selectSql.append(" TE.name 			as techniqueCategoryName, ");
		selectSql.append(" AR.status 		as status, ");
		selectSql.append(" AR.tags 			as tags, ");
		selectSql.append(" AR.type 			as type, ");
		selectSql.append(" US.id 			as cmsUserId, ");
		selectSql.append(" US.username 		as createdUser, ");
		selectSql.append(" AR.created_time 	as createdTime, ");
		selectSql.append(" AR.sent_count	as sentCount ");
		
		fromQuery.append(" FROM ARTICLE AR ");
		fromQuery.append(" 		LEFT JOIN ARTICLE_CATEGORY AG ");
		fromQuery.append(" 			ON AR.article_category_id = AG.id ");
		fromQuery.append(" 		LEFT JOIN GROUP_CATEGORY GC ");
		fromQuery.append(" 			ON AR.group_category_id = GC.id ");
		fromQuery.append(" 		LEFT JOIN TECHNIQUE_CATEGORY TE ");
		fromQuery.append(" 			ON AR.technique_category_id = TE.id ");
		fromQuery.append(" 		LEFT JOIN CMS_USER US ");
		fromQuery.append(" 			ON AR.cms_user_id = US.id ");
		fromQuery.append(" 		LEFT JOIN MEDIA ME ");
		fromQuery.append(" 			ON AR.media_id = ME.id ");
		
		fromQuery.append(" where AR.status != ?  ");
		paramSelect.add(ArticleStatus.DELETED.getValue());
		countParams.add(ArticleStatus.DELETED.getValue());

		
		if (id != null) {
			fromQuery.append(" and AR.id = ? ");
			paramSelect.add(id);
			countParams.add(id);
		}
		
		selectSql.append(fromQuery).append(" ORDER BY AR.created_time DESC");
		
		countQuery.append("SELECT COUNT(*) AS COUNT ").append(fromQuery);

		String[] fieldNames = new String[] { 
				"id", "name", "mediaId",
				"mediaName", "avatarUrl", "avatarThumb", 
				"content",  "contentHtml", "reason", "reasonHtml",
				"articleCategoryId","articleCategoryName",
				"groupCategoryId","groupCategoryName",
				"techniqueCategoryId","techniqueCategoryName", "tags",
				"status", "type", "cmsUserId",
				"createdUser","createdTime","sentCount"
				};
		Type[] fieldTypes = new Type[] { 
				StandardBasicTypes.INTEGER,StandardBasicTypes.STRING, StandardBasicTypes.INTEGER,
				StandardBasicTypes.STRING, StandardBasicTypes.STRING, StandardBasicTypes.STRING, 
				StandardBasicTypes.STRING, StandardBasicTypes.STRING, StandardBasicTypes.STRING, StandardBasicTypes.STRING,
				StandardBasicTypes.INTEGER,StandardBasicTypes.STRING,
				StandardBasicTypes.INTEGER,StandardBasicTypes.STRING, 
				StandardBasicTypes.INTEGER,StandardBasicTypes.STRING, StandardBasicTypes.STRING,
				StandardBasicTypes.INTEGER,StandardBasicTypes.INTEGER, StandardBasicTypes.INTEGER, 
				StandardBasicTypes.STRING, StandardBasicTypes.TIMESTAMP, StandardBasicTypes.INTEGER };

		List<Class<?>> synchronizedClass = new ArrayList<Class<?>>();
		synchronizedClass.add(Article.class);
		synchronizedClass.add(Media.class);
		synchronizedClass.add(ArticleCategory.class);
		synchronizedClass.add(GroupCategory.class);
		synchronizedClass.add(TechniqueCategory.class);
		synchronizedClass.add(CmsUser.class);

		List<ArticleSearchVO> list = null;
		list = repo.getListByQueryAndScalar(ArticleSearchVO.class,
				fieldNames, fieldTypes, selectSql.toString(), paramSelect,
				synchronizedClass);
			
		return list;
		
		
	}
	
	@Override
	public List<Article> getListArticleByArticleCategory(
			Integer articleCategoryId) throws DataAccessException {
		List<Object> params = new ArrayList<Object>();
		String sql = "select * from article where status != ? AND article_category_id = ? ";
		
		params.add(ArticleStatus.DELETED.getValue());
		
		params.add(articleCategoryId);
		
		sql += " ORDER BY created_time DESC ";
		return repo.getListBySQL(Article.class, sql, params);
	} 
	@Override
	public List<Article> getListArticlesByArticleCategoryId(Integer id) throws DataAccessException{
		List<Object> params = new ArrayList<Object>();
		String sql = "SELECT * FROM ARTICLE  where STATUS != ? AND ARTICLE_CATEGORY_ID = ?";
		params.add(ArticleStatus.DELETED.getValue());
		params.add(id);
		return repo.getListBySQL(Article.class, sql, params);
	}
	@Override
	public List<Article> getListArticlesByCmsUserId(Integer id) throws DataAccessException{
		List<Object> params = new ArrayList<Object>();
		String sql = "SELECT * FROM ARTICLE  where STATUS != ? AND CMS_USER_ID = ?";
		params.add(ArticleStatus.DELETED.getValue());
		params.add(id);
		return repo.getListBySQL(Article.class, sql, params);
	}
} 
 
